package com.hshh.controller;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.hshh.util.ExcelUtil;

@Controller
public class ExcelUploadController {

	@RequestMapping(value="/excelUploadTest.file")
	public void getFileDownload(HttpServletRequest request,
			HttpServletResponse response) throws IOException {
		// TODO Auto-generated method stub
		MultipartHttpServletRequest mhsr = (MultipartHttpServletRequest) request;
		Map<String, MultipartFile> files = mhsr.getFileMap();
		
		Set<String> set = files.keySet();
		Iterator<String> iterator =  set.iterator();
		MultipartFile file = null;
		
		List<Object> list = new ArrayList<Object>();
		
		List<Object> excelList = new ArrayList<Object>();
		
		while(iterator.hasNext()) {
			Map<String, Object> map = new HashMap<String, Object>();

			String key = iterator.next();
			file = files.get(key);

			List<Object> exclList = getExcelByJson(request, file);
			//excelList = getExcelByJson(file);
			map.put("sizeB", file.getSize());
			map.put("sizeKB", (double) file.getSize() / 1024);
			map.put("sizeMB", (double) file.getSize() / 1048576);
			map.put("fieldName", file.getName());
			map.put("fileName", file.getOriginalFilename());
			list.add(map);
			
			excelList.add(exclList);
		}
		
		JSONObject json = new JSONObject();
		json.put("file", list);
		json.put("excel", excelList);
		response.setContentType("text/html");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(json.toString());
	}
	
	@RequestMapping("/excel")
	public String excelUpload(){
		return "excel";
	}
	@SuppressWarnings({ "deprecation", "unused" })
	private List<Object> getExcelByJson(HttpServletRequest request, MultipartFile file) throws IOException {
		List<String> fields = (List<String>) request.getAttribute("fields");

		POIFSFileSystem fileSystem = new POIFSFileSystem(file.getInputStream());
		HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);

		HSSFSheet sheet = workbook.getSheetAt(0); // 첫번째 쉬트
		List<Object> list = new ArrayList<Object>();
		Hashtable<String, String> ht = null;

		int rows = sheet.getPhysicalNumberOfRows(); // 행개수
		for (int i = 1; i < rows; i++) {
			ht = new Hashtable<String, String>();

			HSSFRow row = sheet.getRow(i); // row 가져오기
			if (row != null) {
				int cells = row.getLastCellNum(); // 셀 개수 가져오기
				for (short c = 0; c < cells; c++) {

					HSSFCell cell = row.getCell(c);
					if (cell != null) {
						String value = null;
						switch (cell.getCellType()) { // 각셀의 데이터값을 가져올때 맞는데이터형으로 변환한다.
							case HSSFCell.CELL_TYPE_FORMULA:
								value = cell.getCellFormula();
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								value = "" + cell.getNumericCellValue();
								break;
							case HSSFCell.CELL_TYPE_STRING:
								value = "" + cell.getStringCellValue();
								break;
							case HSSFCell.CELL_TYPE_BLANK:
								value = "";
								break;
							case HSSFCell.CELL_TYPE_ERROR:
								value = "" + cell.getErrorCellValue();
								break;
							default:
								value = "";
						}
						// hashtable 에값을 넣어 둔다.
						String checkStr = value.replaceAll(" ", "");
						
						if("".equals(checkStr)) {
							value = "";
						}
						if(fields != null) {
							ht.put(fields.get(cell.getCellNum()), value);
						}
						else {
							ht.put("excel_" + cell.getCellNum(), value);
						}
					} else {
						int cellNum = 99;
						String cellName = "default";
						if(cell != null) {
							cellNum = cell.getCellNum();
							if(fields != null) {
								cellName = fields.get(cellNum);
							}
						}
						if(fields != null) {
							ht.put(cellName, "");
						}
						else {
							ht.put("excel_" + cellNum, "");
						}
						
					}
				}
			}
			
			if(ht.size() > 1){
				Iterator<String> iterator = ht.keySet().iterator();
				boolean flag=  false;
				while(iterator.hasNext()) {
					if(!"".equals(ht.get(iterator.next()))) {
						flag = true;
						break;
					}
				}
				if(flag) {
					list.add(ht);
				}
			}
		}
		return list;
	}
}
